# -*- coding: utf-8 -*-
"""
Created on Mon Jan  8 10:48:29 2018

@author: 006702
"""

import cx_Oracle
import pandas as pd
import numpy as np
from util import GlobalList as gl

workpath = "D:\\code\\factor_matic_server_lx_1120\\"
logpath=workpath+'factor_matic_server.log'

import logging
logging.basicConfig(level=logging.DEBUG,
                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                datefmt='%a, %d %b %Y %H:%M:%S',
                filename=logpath,
                filemode='w')

#获取数据库连接
def getConnection():
    conn=False
    try:
        conn=cx_Oracle.connect('factor_factory/htfactor123@168.61.13.175:1521/ost')
    except cx_Oracle.DatabaseError as msg:
        logging.exception(msg)
        print(msg)
    return conn

# 另一个用户连接数据库,用于插入
def get_connection_fof():
    conn = False
    try:
        conn = cx_Oracle.connect('fof_style/Style_analysis@168.61.13.175:1521/ost')
    except cx_Oracle.DatabaseError as msg:  
        logging.exception(msg)
        print(msg) 
    return conn    

#获取收盘价
def getCloseData(start_date,end_date):
    close_data=pd.DataFrame()
    sql_str='select *  from ' + gl.TABLE_FACTOR_DATA + ' where FACTOR_ID=\'close_bak\' and data_date<=%d and data_date>=%d' %(end_date, start_date)
    close_data=pd.DataFrame(dataSelectFof(sql_str))
    return close_data

#获取因子数据
def getFactorData(start_date,end_date):
    factor_data=pd.DataFrame()    
    sql_str='select *  from ' + gl.TABLE_FACTOR_DATA + '  where FACTOR_ID!=\'close\' and FACTOR_ID!=\'pct_chg_all\'  and data_date<=' +'%d' %end_date + ' and data_date>=' +'%d' %start_date
    factor_data=pd.DataFrame(dataSelectFof(sql_str))
    return factor_data


# 取某个日期前(负，-7)or后(正，+7)N天的交易日日期,date_list[0]为最近的一天
def getNtradeDate(start_date, n):
    date_list = np.empty(abs(n))
    # 两种格式化皆可，下面的更容易懂，且不容易出错
    if n >= 0:
        sql_str = 'select to_number(trade_days) from (select distinct trade_days from \
                      ' + gl.TABLE_CALENDAR + ' where trade_days>= ' + '%s' % start_date + ' \
                      order by trade_days) where  rownum<=' + '%s' % n + ' order by trade_days asc'
    else:
        n = abs(n)
        sql_str = 'select to_number(trade_days) from (select distinct trade_days from \
                      ' + gl.TABLE_CALENDAR + ' where trade_days<=%s   \
                      order by trade_days desc) where  rownum<=%s order by trade_days asc' % (start_date, n)
    date_list = np.array(dataSelect(sql_str)).reshape((n,))
    return date_list        # 返回N行1列的numpy.ndarray


#查询因子信息
def getStyleFactorInfo():
    sql_str ='select  distinct factor_id,style_id from ' + gl.TABLE_FACTOR_INFO 
    factor_list=np.array(dataSelectFof(sql_str))
    return factor_list
    
#查询自选股信息
def getSelfStockInfo(data_date):
    sql_str ='select distinct stock_code,user_id,group_id from M_SELF_STOCK  where  data_date='+'%d' %data_date 
    stock_info=pd.DataFrame(dataSelect(sql_str),columns=['stockcode','user_id','group_id'])
    return stock_info


def getGroupInfo(data_date,group_id,user_id,group_type):
    sql_str ='select distinct stock_code,stock_weight from M_GROUP_INFO WHERE data_date='+'%d' %data_date \
              +' and group_id =\''+group_id+'\''+ ' and user_id =\''+user_id+'\''+' and group_type ='+'%d' %group_type
    stock_info=pd.DataFrame(dataSelect(sql_str))
#    print(sql_str)
    return stock_info


def getGroupReturn(data_date,group_id,user_id):
    sql_str ='select distinct group_id,group_acc_return,user_id from M_GROUP_RETURN WHERE data_date='+'%d' %data_date +' and group_id =\''+group_id+'\'' + ' and user_id =\''+user_id+'\''
    group_info=pd.DataFrame(dataSelect(sql_str),columns=['group_id','group_acc_return','user_id'])
    return group_info


def getUpdateStatus(table_name,data_date):
    sql_str ='select distinct table_status from M_TABLE_STATUS  where data_date ='+'%d' %data_date +' and table_name=\''+table_name +'\''
    update_flag=dataSelect(sql_str)
    return update_flag


# def newsHeatStatusInsert(data_date):
#     sql='insert into M_TABLE_STATUS (DATA_DATE,TABLE_NAME,TABLE_STATUS) VALUES(:1,:2,:3)'
#     news_heat_status=pd.DataFrame()
#     news_heat_status['data_date']=np.tile(data_date,1)
#     news_heat_status['table_name']=np.tile('M_NEWS_HEAT',1)
#     news_heat_status['table_status']=np.tile(1,1)
#
#     news_heat_status_list=news_heat_status.values.tolist()
#     flag=dataInsert(sql,news_heat_status_list)
#     return flag


#每日模型预测结果入库
def stockRatingInsert(stock_info):
    if(0==len(stock_info)):
        logging.error('The stock info  is empty,plz check')
        return
    sql='insert into ' + gl.TABLE_FOF_MODEL + ' (DATA_DATA,STOCK_CODE,Y_PRED,LN_CAPITAL) VALUES(:1,:2,:3,:4)'
    stock_info_df=pd.DataFrame()
    stock_info_df['data_date']=stock_info.iloc[:,0].values
    stock_info_df['stock_code']=stock_info.iloc[:,1].values
    stock_info_df['stock_rating']=stock_info.iloc[:,2].values
    stock_info_df['market_value']=stock_info.iloc[:,3].values

    stock_info_df = stock_info_df.where(stock_info_df.notna(), None)

    stock_info_list=stock_info_df.values.tolist()
    flag=dataInsert(sql,stock_info_list)                
    return flag


#标准化之后的因子数据入库
def normalDataInsert(factor_normal_info):
    if(0==len(factor_normal_info)):
        logging.error('The factor data is empty,plz check!')
        return
    sql='insert into M_FACTOR_NORMAL_DATA (DATA_DATE,STOCK_CODE,FACTOR_ID,FACTOR_VALUE) VALUES (:1,:2,:3,:4)'
    factor_normal_df=pd.DataFrame()
    factor_normal_df['data_date']=factor_normal_info.iloc[:,0].values
    factor_normal_df['stock_code']=factor_normal_info.iloc[:,1].values
    factor_normal_df['factor_id']=factor_normal_info.iloc[:,2].values
    factor_normal_df['factor_value']=factor_normal_info.iloc[:,3].values
    factor_normal_list=factor_normal_df.values.tolist()
    # flag=dataInsert(sql,factor_normal_list)
    flag = 1
    return flag
                    
                    
# #风格因子表现入库
# def styleReturnInsert(data_date,style_factor_return):
#     if(0==len(style_factor_return)):
#         logging.error('The style_factor_return input is empty,plz check!')
#     sql='insert into M_FACTOR_RETURN (DATA_DATE,FACTOR_ID,FACTOR_RETURN) VALUES(:1,:2,:3)'
#     factor_return_df=pd.DataFrame()
#     factor_return_df['data_date']=np.tile(data_date,len(style_factor_return))
#     factor_return_df['factor_id']= style_factor_return.index
#     factor_return_df['factor_return']= style_factor_return.values
#     factor_return_list=factor_return_df.values.tolist()
#     flag=dataInsert(sql,factor_return_list)
#     return flag





# #因子暴露入库
# def factorExposureInsert(data_date,group_id,factor_exposure,benchmark,user_id):
#     if( 0==len(group_id) or 0==len(factor_exposure) or 0==len(benchmark)):
#         logging.error('The style_factor_return input is empty,plz check!')
#     sql='insert into M_FACTOR_EXPOSURE (DATA_DATE,FACTOR_ID,GROUP_ID,BENCHMARK,EXPOSURE_VALUE,USER_ID) VALUES(:1,:2,:3,:4,:5,:6)'
#     factor_exposure_df=pd.DataFrame()
#     factor_exposure_df['data_date']=np.tile(data_date,len(factor_exposure))
#     factor_exposure_df['factor_id']=factor_exposure.index
#     factor_exposure_df['group_id']=np.tile(group_id,len(factor_exposure))
#     factor_exposure_df['benchmark']=np.tile(benchmark,len(factor_exposure))
#     factor_exposure_df['exposure_value']=factor_exposure.values
#     factor_exposure_df['user_id']=np.tile(user_id,len(factor_exposure))
#     factor_exposure_list=factor_exposure_df.values.tolist()
#     dataInsert(sql,factor_exposure_list)

# #组合每日信息入库
# def groupInfoInsert(data_date,stock_list,stock_weight,group_id,group_type,benchmark,user_id):
#     if( 0==len(stock_list) or 0==len(stock_weight)):
#         logging.error('The style_factor_return input is empty,plz check!')
#     group_info_df=pd.DataFrame()
#     group_info_df['data_date']=np.tile(data_date,len(stock_list))
#     group_info_df['stock_code']=stock_list
#     group_info_df['stock_weight']=stock_weight
#     group_info_df['group_id']=np.tile(group_id,len(stock_list))
#     group_info_df['group_type']=np.tile(group_type,len(stock_list))
#     group_info_df['benchmark']=np.tile(benchmark,len(stock_list))
#     group_info_df['user_id']=np.tile(user_id,len(stock_list))
#     sql='insert into M_GROUP_INFO (DATA_DATE,STOCK_CODE,STOCK_WEIGHT,GROUP_ID,GROUP_TYPE,BENCHMARK,USER_ID) VALUES(:1,:2,:3,:4,:5,:6,:7)'
#     group_info_list=group_info_df.values.tolist()
#     dataInsert(sql,group_info_list)


# def groupReturnInsert(group_return,group_id,user_id):
#     if(0==len(group_return) ):
#         logging.error('The style_factor_return input is empty,plz check!')
#     sql='insert into M_GROUP_RETURN (DATA_DATE,GROUP_ID,GROUP_RETURN,GROUP_ACC_RETURN,USER_ID) VALUES(:1,:2,:3,:4,:5)'
#
#     group_return_df=pd.DataFrame()
#     group_return_df['data_date']=pd.Series(data=group_return['data_date'])
#     group_return_df['group_id']=group_id
#     group_return_df['group_return']=group_return['group_return']
#     group_return_df['group_acc_return']=group_return['group_acc_return']
#     group_return_df['user_id']=user_id
#     group_return_list=group_return_df.values.tolist()
#     dataInsert(sql,group_return_list)


#数据查询操作
def dataSelectFof(sql):
    conn=get_connection_fof()
    if False == conn:
        logging.error('The oracle connection is false,plz check!')
    cur=conn.cursor()    
    try:
        res = cur.execute(sql)
        result=res.fetchall()
    except Exception as e:
        result=0
        logging.exception(e)
        print(e)
    finally:
        cur.close()
        conn.close()
    return result



#数据查询操作
def dataSelect(sql):
    conn=getConnection()
    if False == conn:
        logging.error('The oracle connection is false,plz check!')
    cur=conn.cursor()    
    try:
        res = cur.execute(sql)
        result=res.fetchall()
    except Exception as e:
        result=0
        logging.exception(e)
        print(e)
    finally:
        cur.close()
        conn.close()
    return result



#数据库插入
def dataInsert(sql,data):
    conn=get_connection_fof()
    if False == conn:
        logging.error('The oracle connection is false,plz check!')
    cur=conn.cursor()
    try:
        cur.executemany(sql,data)
        conn.commit()
        flag=1
    except Exception as e:
        flag=0
        logging.exception(e)
        print(e)
    finally:
        cur.close()
        conn.close()
    return flag


    


    
        
    